<!DOCTYPE html>
<html lang="">
    <!-- title -->




<!-- keywords -->




<head><meta name="generator" content="Hexo 3.8.0">
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <meta name="author" content="Eric">
    <meta name="renderer" content="webkit">
    <meta name="copyright" content="Eric">
    
    <meta name="keywords" content="hexo,hexo-theme,hexo-blog">
    
    <meta name="description" content="">
    <meta http-equiv="Cache-control" content="no-cache">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <title>Mysql死锁分析 · VIK&#39;s blog</title>
    <style type="text/css">
    @font-face {
        font-family: 'Oswald-Regular';
        src: url("/blog/font/Oswald-Regular.ttf");
    }

    body {
        margin: 0;
    }

    header,
    footer,
    .back-top,
    .sidebar,
    .container,
    .site-intro-meta,
    .toc-wrapper {
        display: none;
    }

    .site-intro {
        position: relative;
        z-index: 3;
        width: 100%;
        /* height: 50vh; */
        overflow: hidden;
    }

    .site-intro-placeholder {
        position: absolute;
        z-index: -2;
        top: 0;
        left: 0;
        width: calc(100% + 300px);
        height: 100%;
        background: repeating-linear-gradient(-45deg, #444 0, #444 80px, #333 80px, #333 160px);
        background-position: center center;
        transform: translate3d(-226px, 0, 0);
        animation: gradient-move 2.5s ease-out 0s infinite;
    }

    @keyframes gradient-move {
        0% {
            transform: translate3d(-226px, 0, 0);
        }
        100% {
            transform: translate3d(0, 0, 0);
        }
    }

</style>

    <link rel="preload" href="/blog/css/style.css?v=20180824" as="style" onload="this.onload=null;this.rel='stylesheet'">
    <link rel="stylesheet" href="/blog/css/mobile.css?v=20180824" media="(max-width: 980px)">
    
    <link rel="preload" href="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.2.5/jquery.fancybox.min.css" as="style" onload="this.onload=null;this.rel='stylesheet'">
    
    <!-- /*! loadCSS. [c]2017 Filament Group, Inc. MIT License */
/* This file is meant as a standalone workflow for
- testing support for link[rel=preload]
- enabling async CSS loading in browsers that do not support rel=preload
- applying rel preload css once loaded, whether supported or not.
*/ -->
<script>
(function( w ){
	"use strict";
	// rel=preload support test
	if( !w.loadCSS ){
		w.loadCSS = function(){};
	}
	// define on the loadCSS obj
	var rp = loadCSS.relpreload = {};
	// rel=preload feature support test
	// runs once and returns a function for compat purposes
	rp.support = (function(){
		var ret;
		try {
			ret = w.document.createElement( "link" ).relList.supports( "preload" );
		} catch (e) {
			ret = false;
		}
		return function(){
			return ret;
		};
	})();

	// if preload isn't supported, get an asynchronous load by using a non-matching media attribute
	// then change that media back to its intended value on load
	rp.bindMediaToggle = function( link ){
		// remember existing media attr for ultimate state, or default to 'all'
		var finalMedia = link.media || "all";

		function enableStylesheet(){
			link.media = finalMedia;
		}

		// bind load handlers to enable media
		if( link.addEventListener ){
			link.addEventListener( "load", enableStylesheet );
		} else if( link.attachEvent ){
			link.attachEvent( "onload", enableStylesheet );
		}

		// Set rel and non-applicable media type to start an async request
		// note: timeout allows this to happen async to let rendering continue in IE
		setTimeout(function(){
			link.rel = "stylesheet";
			link.media = "only x";
		});
		// also enable media after 3 seconds,
		// which will catch very old browsers (android 2.x, old firefox) that don't support onload on link
		setTimeout( enableStylesheet, 3000 );
	};

	// loop through link elements in DOM
	rp.poly = function(){
		// double check this to prevent external calls from running
		if( rp.support() ){
			return;
		}
		var links = w.document.getElementsByTagName( "link" );
		for( var i = 0; i < links.length; i++ ){
			var link = links[ i ];
			// qualify links to those with rel=preload and as=style attrs
			if( link.rel === "preload" && link.getAttribute( "as" ) === "style" && !link.getAttribute( "data-loadcss" ) ){
				// prevent rerunning on link
				link.setAttribute( "data-loadcss", true );
				// bind listeners to toggle media back
				rp.bindMediaToggle( link );
			}
		}
	};

	// if unsupported, run the polyfill
	if( !rp.support() ){
		// run once at least
		rp.poly();

		// rerun poly on an interval until onload
		var run = w.setInterval( rp.poly, 500 );
		if( w.addEventListener ){
			w.addEventListener( "load", function(){
				rp.poly();
				w.clearInterval( run );
			} );
		} else if( w.attachEvent ){
			w.attachEvent( "onload", function(){
				rp.poly();
				w.clearInterval( run );
			} );
		}
	}


	// commonjs
	if( typeof exports !== "undefined" ){
		exports.loadCSS = loadCSS;
	}
	else {
		w.loadCSS = loadCSS;
	}
}( typeof global !== "undefined" ? global : this ) );
</script>

    <link rel="icon" href="/blog/assets/favicon.jpg">
    <link rel="preload" href="https://cdn.jsdelivr.net/npm/webfontloader@1.6.28/webfontloader.min.js" as="script">
    <link rel="preload" href="https://cdn.jsdelivr.net/npm/jquery@3.3.1/dist/jquery.min.js" as="script">
    <link rel="preload" href="/blog/scripts/main.js" as="script">
    <link rel="preload" as="font" href="/blog/font/Oswald-Regular.ttf" crossorigin="">
    <link rel="preload" as="font" href="https://at.alicdn.com/t/font_327081_1dta1rlogw17zaor.woff" crossorigin="">
    
    <!-- fancybox -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.2.5/jquery.fancybox.min.js" defer></script>
    <!-- 百度统计  -->
    
    <!-- 谷歌统计  -->
    
</head>

    
        <body class="post-body">
    
    
<header class="header">

    <div class="read-progress"></div>
    <div class="header-sidebar-menu">&#xe775;</div>
    <!-- post页的toggle banner  -->
    
    <div class="banner">
            <div class="blog-title">
                <a href="/blog/">VIK&#39;s blog</a>
            </div>
            <div class="post-title">
                <a href="#" class="post-name">Mysql死锁分析</a>
            </div>
    </div>
    
    <a class="home-link" href="/blog/">VIK's blog</a>
</header>
    <div class="wrapper">
        <div class="site-intro" style="







height:50vh;
">
    
    <!-- 主页  -->
    
    
    <!-- 404页  -->
            
    <div class="site-intro-placeholder"></div>
    <div class="site-intro-img" style="background-image: url(/blog/intro/post-bg.jpg)"></div>
    <div class="site-intro-meta">
        <!-- 标题  -->
        <h1 class="intro-title">
            <!-- 主页  -->
            
            Mysql死锁分析
            <!-- 404 -->
            
        </h1>
        <!-- 副标题 -->
        <p class="intro-subtitle">
            <!-- 主页副标题  -->
            
            
            <!-- 404 -->
            
        </p>
        <!-- 文章页meta -->
        
            <div class="post-intros">
                <!-- 文章页标签  -->
                
                
                    <div class="post-intro-read">
                        <span>字数统计: <span class="post-count word-count">2.5k</span>阅读时长: <span class="post-count reading-time">9 min</span></span>
                    </div>
                
                <div class="post-intro-meta">
                    <span class="post-intro-calander iconfont-archer">&#xe676;</span>
                    <span class="post-intro-time">2019/03/03</span>
                    
                    <span id="busuanzi_container_page_pv" class="busuanzi-pv">
                        <span class="iconfont-archer">&#xe602;</span>
                        <span id="busuanzi_value_page_pv"></span>
                    </span>
                    
                    <span class="shareWrapper">
                        <span class="iconfont-archer shareIcon">&#xe71d;</span>
                        <span class="shareText">Share</span>
                        <ul class="shareList">
                            <li class="iconfont-archer share-qr" data-type="qr">&#xe75b;
                                <div class="share-qrcode"></div>
                            </li>
                            <li class="iconfont-archer" data-type="weibo">&#xe619;</li>
                            <li class="iconfont-archer" data-type="qzone">&#xe62e;</li>
                            <li class="iconfont-archer" data-type="twitter">&#xe634;</li>
                            <li class="iconfont-archer" data-type="facebook">&#xe67a;</li>
                        </ul>
                    </span>
                </div>
            </div>
        
    </div>
</div>
        <script>
 
  // get user agent
  var browser = {
    versions: function () {
      var u = window.navigator.userAgent;
      return {
        userAgent: u,
        trident: u.indexOf('Trident') > -1, //IE内核
        presto: u.indexOf('Presto') > -1, //opera内核
        webKit: u.indexOf('AppleWebKit') > -1, //苹果、谷歌内核
        gecko: u.indexOf('Gecko') > -1 && u.indexOf('KHTML') == -1, //火狐内核
        mobile: !!u.match(/AppleWebKit.*Mobile.*/), //是否为移动终端
        ios: !!u.match(/\(i[^;]+;( U;)? CPU.+Mac OS X/), //ios终端
        android: u.indexOf('Android') > -1 || u.indexOf('Linux') > -1, //android终端或者uc浏览器
        iPhone: u.indexOf('iPhone') > -1 || u.indexOf('Mac') > -1, //是否为iPhone或者安卓QQ浏览器
        iPad: u.indexOf('iPad') > -1, //是否为iPad
        webApp: u.indexOf('Safari') == -1, //是否为web应用程序，没有头部与底部
        weixin: u.indexOf('MicroMessenger') == -1, //是否为微信浏览器
        uc: u.indexOf('UCBrowser') > -1 //是否为android下的UC浏览器
      };
    }()
  }
  console.log("userAgent:" + browser.versions.userAgent);

  // callback
  function fontLoaded() {
    console.log('font loaded');
    if (document.getElementsByClassName('site-intro-meta')) {
      document.getElementsByClassName('intro-title')[0].classList.add('intro-fade-in');
      document.getElementsByClassName('intro-subtitle')[0].classList.add('intro-fade-in');
      var postIntros = document.getElementsByClassName('post-intros')[0]
      if (postIntros) {
        postIntros.classList.add('post-fade-in');
      }
    }
  }

  // UC不支持跨域，所以直接显示
  function asyncCb(){
    if (browser.versions.uc) {
      console.log("UCBrowser");
      fontLoaded();
    } else {
      WebFont.load({
        custom: {
          families: ['Oswald-Regular']
        },
        loading: function () {  //所有字体开始加载
          // console.log('loading');
        },
        active: function () {  //所有字体已渲染
          fontLoaded();
        },
        inactive: function () { //字体预加载失败，无效字体或浏览器不支持加载
          console.log('inactive: timeout');
          fontLoaded();
        },
        timeout: 5000 // Set the timeout to two seconds
      });
    }
  }

  function asyncErr(){
    console.warn('script load from CDN failed, will load local script')
  }

  // load webfont-loader async, and add callback function
  function async(u, cb, err) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (cb) { o.addEventListener('load', function (e) { cb(null, e); }, false); }
    if (err) { o.addEventListener('error', function (e) { err(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }

  var asyncLoadWithFallBack = function(arr, success, reject) {
      var currReject = function(){
        reject()
        arr.shift()
        if(arr.length)
          async(arr[0], success, currReject)
        }

      async(arr[0], success, currReject)
  }

  asyncLoadWithFallBack([
    "https://cdn.jsdelivr.net/npm/webfontloader@1.6.28/webfontloader.min.js", 
    "https://cdn.bootcss.com/webfont/1.6.28/webfontloader.js",
    "/blog/lib/webfontloader.min.js"
  ], asyncCb, asyncErr)
</script>        
        <img class="loading" src="/blog/assets/loading.svg" style="display: block; margin: 6rem auto 0 auto; width: 6rem; height: 6rem;" />
        <div class="container container-unloaded">
            <main class="main post-page">
    <article class="article-entry">
        <h3 id="1-存储引擎"><a href="#1-存储引擎" class="headerlink" title="1.存储引擎"></a>1.存储引擎</h3><p>Mysql的存储引擎一般常用的有 MyISAM和Innodb</p>
<table>
<thead>
<tr>
<th>对比项</th>
<th>InnoDB</th>
<th>MyIsAM</th>
</tr>
</thead>
<tbody>
<tr>
<td>事务</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>锁</td>
<td>支持MVCC行锁</td>
<td>表锁</td>
</tr>
<tr>
<td>外键</td>
<td>支持</td>
<td>不支持</td>
</tr>
<tr>
<td>存储空间</td>
<td>存储空间由于需要高速缓存，较大</td>
<td>可压缩</td>
</tr>
<tr>
<td>适用场景</td>
<td>有一定量的update和Insert</td>
<td>大量的select</td>
</tr>
</tbody>
</table>
<ul>
<li><p>查看存储引擎</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like &apos;%storage_engine%&apos;;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="2-锁"><a href="#2-锁" class="headerlink" title="2. 锁"></a>2. 锁</h3><h4 id="2-1-MyISAM锁类型"><a href="#2-1-MyISAM锁类型" class="headerlink" title="2.1 MyISAM锁类型"></a>2.1 MyISAM锁类型</h4><ul>
<li>表共享锁(Table Read Lock)  -读锁</li>
<li>表独占写锁 (Table Write Lock) -写锁</li>
<li>读锁之前之前不阻塞，读锁与写锁、写锁之间阻塞</li>
</ul>
<h4 id="2-2-InnoDB锁"><a href="#2-2-InnoDB锁" class="headerlink" title="2.2 InnoDB锁"></a>2.2 InnoDB锁</h4><ul>
<li><p>S-共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。</p>
</li>
<li><p>X-排他锁: 又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。</p>
</li>
<li><p>意向锁:</p>
<ul>
<li>意向共享锁:表达一个事务想要获取一张表中某几行的共享锁。</li>
<li>意向排他锁:表达一个事务想要获取一张表中某几行的排他锁。</li>
</ul>
</li>
<li><p>自增长锁</p>
<ul>
<li><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE `test_inc` (</span><br><span class="line">`id` int(11) NOT NULL AUTO_INCREMENT,</span><br><span class="line">PRIMARY KEY (`id`)</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span><br><span class="line"></span><br><span class="line">select last_insert_id();//获得最后插入的数字</span><br></pre></td></tr></table></figure>
</li>
<li><p>表级锁</p>
</li>
<li>适合并发小的场景</li>
<li>主从模式容易出错</li>
</ul>
</li>
</ul>
<h3 id="3-事务"><a href="#3-事务" class="headerlink" title="3. 事务"></a>3. 事务</h3><p>事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。</p>
<ul>
<li>原性性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。</li>
<li>一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。</li>
<li>隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。</li>
<li>持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持</li>
</ul>
<h4 id="3-1-事务解决的问题"><a href="#3-1-事务解决的问题" class="headerlink" title="3.1 事务解决的问题"></a>3.1 事务解决的问题</h4><h5 id="3-1-1-脏读"><a href="#3-1-1-脏读" class="headerlink" title="3.1.1 脏读"></a>3.1.1 脏读</h5><table>
<thead>
<tr>
<th>时间点</th>
<th>事务A</th>
<th>事务B</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>begin;</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>select * from user where id = 1;</td>
<td>begin;</td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>update user set namm = ‘test’ where id = 1;</td>
</tr>
<tr>
<td>4</td>
<td>select * from user where id = 1;</td>
<td></td>
</tr>
<tr>
<td>5</td>
<td>commit;</td>
<td>commit;</td>
</tr>
</tbody>
</table>
<p>事务A在4中的查询出的结果其实是事务B修改后的，如果事务B回滚了，那么A中查出来的就不是实际的结果</p>
<h5 id="3-1-2-不可重复读"><a href="#3-1-2-不可重复读" class="headerlink" title="3.1.2  不可重复读"></a>3.1.2  不可重复读</h5><p>在同一个事务中，多次读取同一数据返回的结果不同，和脏读不同的是这里读取的是已经提交过后的。</p>
<table>
<thead>
<tr>
<th></th>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>时间点</td>
<td>事务A</td>
<td>事务B</td>
</tr>
<tr>
<td>1</td>
<td>begin;</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>select * from user where id = 1;</td>
<td>begin;</td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>update user set namm = ‘test’ where id = 1;</td>
</tr>
<tr>
<td>4</td>
<td></td>
<td>commit;</td>
</tr>
<tr>
<td>5</td>
<td>select * from user where id = 1;</td>
<td></td>
</tr>
<tr>
<td>6</td>
<td>commit;</td>
<td></td>
</tr>
<tr>
<td>在事务B中提交的操作在事务A第二次查询之前，<br>但是依然读到了<br>事务B的更新结果，也破坏了事务的隔离性。</td>
<td></td>
</tr>
</tbody>
</table>
<h5 id="3-1-3-幻读"><a href="#3-1-3-幻读" class="headerlink" title="3.1.3 幻读"></a>3.1.3 幻读</h5><p>一个事务读到另一个事务已提交的insert数据。</p>
<table>
<thead>
<tr>
<th></th>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>时间点</td>
<td>事务A</td>
<td>事务B</td>
</tr>
<tr>
<td>1</td>
<td>begin;</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>select * from user where id &gt; 1;</td>
<td>begin;</td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>insert user select 2;</td>
</tr>
<tr>
<td>4</td>
<td></td>
<td>commit;</td>
</tr>
<tr>
<td>5</td>
<td>select * from user where id &gt; 1;</td>
<td></td>
</tr>
<tr>
<td>6</td>
<td>commit;</td>
</tr>
</tbody>
</table>
<p>在事务A中查询了两次id大于1的，在第一次id大于1查询结果中没有数据，但是由于事务B插入了一条Id=2的数据，导致事务A后面插入的时候报主键冲突</p>
<table>
<thead>
<tr>
<th></th>
<th>自己的话总结</th>
</tr>
</thead>
<tbody>
<tr>
<td>脏读</td>
<td>第二次读到的数据可能是假的</td>
</tr>
<tr>
<td>不可重复读</td>
<td>第二次读到的数据跟第一次不一样，因为被别的事务干扰了</td>
</tr>
<tr>
<td>幻读</td>
<td>明明确认过不存在后，等会再查询发现多了一条。<br>“不可重复读则是对比数据本身，幻读是条数上的对比”</td>
</tr>
</tbody>
</table>
<h3 id="4-InnoDB锁算法"><a href="#4-InnoDB锁算法" class="headerlink" title="4. InnoDB锁算法"></a>4. InnoDB锁算法</h3><h4 id="4-1-记录锁-Record-Lock"><a href="#4-1-记录锁-Record-Lock" class="headerlink" title="4.1 记录锁(Record-Lock)"></a>4.1 记录锁(Record-Lock)</h4><p>记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录。</p>
<ul>
<li>如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住.</li>
<li>如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。</li>
<li>如果要锁的列没有索引，则会进行全表记录加锁。</li>
</ul>
<h4 id="4-2-间隙锁-Gap锁"><a href="#4-2-间隙锁-Gap锁" class="headerlink" title="4.2 间隙锁(Gap锁)"></a>4.2 间隙锁(Gap锁)</h4><p>锁定某一个范围，间隙锁又叫gap锁，其不会阻塞其他的gap锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。</p>
<h4 id="4-3-next-key锁"><a href="#4-3-next-key锁" class="headerlink" title="4.3 next-key锁"></a>4.3 next-key锁</h4><p>这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认)，Innodb对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引则会退化成只使用记录锁。为什么呢? 因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据会造成幻读</p>
<h4 id="4-3-意向锁"><a href="#4-3-意向锁" class="headerlink" title="4.3 意向锁"></a>4.3 意向锁</h4><ul>
<li><p>意向锁是InnoDB支持表锁、行锁共存而使用的</p>
</li>
<li><p>意向锁分为</p>
<ul>
<li><strong>意向共享锁</strong>(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁</li>
<li><strong>意向排它锁</strong>(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁</li>
</ul>
</li>
<li><p>| .    | IX   | IS   | X    | S    |<br>| —- | —- | —- | —- | —- |<br>| IX   | 兼容 | 兼容 | 冲突 | 冲突 |<br>| IS   | 兼容 | 兼容 | 冲突 | 兼容 |<br>| X    | 冲突 | 冲突 | 冲突 | 冲突 |<br>| S    | 冲突 | 兼容 | 冲突 | 兼容 |</p>
<p>插入意向锁</p>
<ul>
<li><p>间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的</p>
</li>
<li><p>多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此</p>
</li>
<li><p>假设插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。</p>
<p>那么这个插入意向锁的作用是什么？</p>
<p>1、为了唤起等待。由于该间隙已经有锁，插入时必须堵塞。插入 意向锁的作用具有堵塞功能。</p>
<p>2、插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？</p>
<p>​     间隙锁之间不相互排斥。不可以堵塞即唤起等待，会造成幻读。</p>
<p>3、为什么不使用记录锁或next-key锁？</p>
<p>​      申请了记录所或next-key锁，next-key锁之间可能相互排斥，即影响insert的并发性。</p>
</li>
</ul>
</li>
</ul>
<h4 id="4-3-MVVC"><a href="#4-3-MVVC" class="headerlink" title="4.3 MVVC"></a>4.3 MVVC</h4><p>在InnoDB中，在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。通过版本号和行锁，从而提高数据库系统并发性能。</p>
<p>在MVCC中，对于读操作可以分为两种读:</p>
<ul>
<li>快照读:读取的历史数据，简单的select语句，不加锁，MVCC实现可重复读，使用的是MVCC机制读取undo中的已经提交的数据。所以它的读取是非阻塞的。</li>
<li>当前读:需要加锁的语句,update,insert,delete,select…for update等等都是当前读。</li>
</ul>
<p>在RR隔离级别下的快照读，不是以begin事务开始的时间点作为snapshot建立时间点，而是以第一条select语句的时间点作为snapshot建立的时间点。以后的select都会读取当前时间点的快照值。</p>
<h3 id="5-死锁"><a href="#5-死锁" class="headerlink" title="5. 死锁"></a>5. 死锁</h3><ul>
<li>示例1</li>
</ul>
<!--S锁是共享锁，X锁是互斥锁。一般来说X锁和S，X锁都互斥，S锁和S锁不互斥。-->
<table>
<thead>
<tr>
<th>时间线</th>
<th>事务1</th>
<th>事务2</th>
<th>事务3</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>insert into xx</td>
<td>insert into xx</td>
<td>insert into xx</td>
</tr>
<tr>
<td>2</td>
<td>获取当前行X锁</td>
<td></td>
<td></td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>需要检测唯一索引是否冲突获取S锁，阻塞</td>
<td>需要检测唯一索引是否冲突获取S锁，阻塞</td>
</tr>
<tr>
<td>4</td>
<td>commit;</td>
<td>获取到到S锁</td>
<td>获取到到S锁</td>
</tr>
<tr>
<td>5</td>
<td></td>
<td>发现唯一索引冲突，执行Update语句(此时S锁未释放)</td>
<td>发现唯一索引冲突，执行Update语句(此时S锁未释放)</td>
</tr>
<tr>
<td>6</td>
<td></td>
<td>获取该行的X锁，被事务3的S锁阻塞</td>
<td>获取该行的X锁，被事务2的S锁阻塞</td>
</tr>
<tr>
<td>7</td>
<td></td>
<td>发现死锁，回滚该事务</td>
<td>update成功,commit;</td>
</tr>
</tbody>
</table>
<ul>
<li>分析<ul>
<li>死锁的关键是需要获取S锁(插入前需要判断索引唯一)</li>
<li>两个事务互相等待S锁的释放，才能获取到X锁来进行update</li>
</ul>
</li>
<li><p>解决办法</p>
<ul>
<li><ol>
<li>将RR隔离级别，降低成RC隔离级别。这里RC隔离级别会用快照读，从而不会加S锁。</li>
</ol>
</li>
<li><ol start="2">
<li>再插入的时候使用select * for update,加X锁，从而不会加S锁。</li>
</ol>
</li>
<li><ol start="3">
<li>可以提前加上分布式锁，可以利用Redis,或者ZK等</li>
</ol>
</li>
<li>第一种方法不太现实，毕竟隔离级别不能轻易的修改。第三种方法又比较麻烦。第二种方法性价比比较高</li>
</ul>
</li>
<li><p>示例2</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE user (</span><br><span class="line"></span><br><span class="line">	id int(11) unsigned NOT NULL AUTO_INCREMENT,</span><br><span class="line"></span><br><span class="line">	name varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL,</span><br><span class="line"></span><br><span class="line">	comment varchar(11) CHARACTER SET utf8 DEFAULT NULL,</span><br><span class="line"></span><br><span class="line">	PRIMARY KEY (id),</span><br><span class="line"></span><br><span class="line">	KEY index_name (name)</span><br><span class="line"></span><br><span class="line">) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<table>
<thead>
<tr>
<th>时间点</th>
<th>事务A</th>
<th>事务B</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>begin;</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>select * from user where name = ‘555’ for update;</td>
<td>begin;</td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>insert user select 31,’556’,’556’;</td>
</tr>
<tr>
<td>4</td>
<td></td>
<td>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</td>
</tr>
</tbody>
</table>
<ul>
<li>分析<ul>
<li>在事务A中给555加了Next-key锁(name为非索引列)，事务B插入的时候会首先进行插入意向锁的插入。事务B由于间隙锁和插入意向锁的冲突，导致了阻塞。</li>
<li>非唯一索引加next-key锁由于不能确定明确的行数有可能其他事务在你查询的过程中，再次添加这个索引的数据，导致隔离性遭到破坏，也就是幻读</li>
</ul>
</li>
</ul>
<table>
<thead>
<tr>
<th></th>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>时间点</td>
<td>事务A</td>
<td>事务B</td>
</tr>
<tr>
<td>1</td>
<td>begin;</td>
<td></td>
</tr>
<tr>
<td>2</td>
<td>select * from user where id = 25 for update;</td>
<td>begin;</td>
</tr>
<tr>
<td>3</td>
<td></td>
<td>insert user select 26,’666’,’666’;</td>
</tr>
<tr>
<td>4</td>
<td></td>
<td>Query OK, 1 row affected (0.00 sec)</td>
</tr>
<tr>
<td>Records: 1 Duplicates: 0 Warnings: 0</td>
<td></td>
</tr>
</tbody>
</table>
<ul>
<li>分析<ul>
<li>唯一索引由于明确了唯一的数据行，所以不需要添加间隙锁解决幻读。</li>
</ul>
</li>
</ul>

    </article>
    <!-- license  -->
    
        <div class="license-wrapper">
            <p>原文作者：<a href="http://fantastyj.gitee.io/blog">Eric</a>
            </p><p>原文链接：<a href="http://fantastyj.gitee.io/blog/2019/03/03/Mysql死锁分析/">http://fantastyj.gitee.io/blog/2019/03/03/Mysql死锁分析/</a>
            </p><p>发表日期：<a href="http://fantastyj.gitee.io/blog/2019/03/03/Mysql死锁分析/">March 3rd 2019, 11:07:58 am</a>
            </p><p>更新日期：<a href="http://fantastyj.gitee.io/blog/2019/03/03/Mysql死锁分析/">March 3rd 2019, 5:31:39 pm</a>
            </p><p>版权声明：本文采用<a rel="license" href="http://creativecommons.org/licenses/by-nc/4.0/">知识共享署名-非商业性使用 4.0 国际许可协议</a>进行许可</p>
        </div>
    
    <!-- paginator  -->
    <ul class="post-paginator">
        <li class="next">
            
                <div class="nextSlogan">Next Post</div>
                <a href="/blog/2020/11/07/设计模式-工厂模式/" title="设计模式-工厂模式">
                    <div class="nextTitle">设计模式-工厂模式</div>
                </a>
            
        </li>
        <li class="previous">
            
                <div class="prevSlogan">Previous Post</div>
                <a href="/blog/2019/02/23/Phoenix部署及应用/" title="Phoenix部署及应用">
                    <div class="prevTitle">Phoenix部署及应用</div>
                </a>
            
        </li>
    </ul>
    <!-- 评论插件 -->
    <!-- 来必力City版安装代码 -->

<!-- City版安装代码已完成 -->
    
    
    <!-- partial('_partial/comment/changyan') -->
    <!--PC版-->


    
    
    <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
    <script src="//unpkg.com/valine/dist/Valine.min.js"></script>
    <div id="comment"></div>
    <script>
    new Valine({
        el: '#comment' ,
        notify:false, 
        verify:false, 
        appId: "xsrAgYKsf6uAaBjhBNhCnzTC-9Nh9j0Va",
        appKey: "MRd2L7iitgfwOndKkXrnxCUs",
        placeholder: "欢迎留言分享和讨论~",
        path:window.location.pathname, 
        avatar:'mm' 
    });
    </script>


    <!-- 评论 -->
</main>
            <!-- profile -->
            
        </div>
        <footer class="footer footer-unloaded">
    <!-- social  -->
    
    <div class="social">
        
    
        
            
                <a href="mailto:465612025@qq.com" class="iconfont-archer email" title="email"></a>
            
        
    
        
            
                <a href="//github.com/WhoamiV" class="iconfont-archer github" target="_blank" title="github"></a>
            
        
    
        
            
                <span class="iconfont-archer wechat" title="wechat">
                  
                  <img class="profile-qr" src="/blog/assets/wechat_qr_code.png">
                </span>
            
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    
        
    

    </div>
    
    <!-- powered by Hexo  -->
    <div class="copyright">
        <span id="hexo-power">Powered by <a href="https://hexo.io/" target="_blank">Hexo</a></span><span class="iconfont-archer power">&#xe635;</span><span id="theme-info">theme <a href="https://github.com/fi3ework/hexo-theme-archer" target="_blank">Archer</a></span>
    </div>
    <!-- 不蒜子  -->
    
    <div class="busuanzi-container">
    
     
    <span id="busuanzi_container_site_pv">PV: <span id="busuanzi_value_site_pv"></span> :)</span>
    
    </div>
    
</footer>
    </div>
    <!-- toc -->
    
    <div class="toc-wrapper" style=
    







top:50vh;

    >
        <div class="toc-catalog">
            <span class="iconfont-archer catalog-icon">&#xe613;</span><span>CATALOG</span>
        </div>
        <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-存储引擎"><span class="toc-number">1.</span> <span class="toc-text">1.存储引擎</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-锁"><span class="toc-number">2.</span> <span class="toc-text">2. 锁</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#2-1-MyISAM锁类型"><span class="toc-number">2.1.</span> <span class="toc-text">2.1 MyISAM锁类型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-2-InnoDB锁"><span class="toc-number">2.2.</span> <span class="toc-text">2.2 InnoDB锁</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-事务"><span class="toc-number">3.</span> <span class="toc-text">3. 事务</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-事务解决的问题"><span class="toc-number">3.1.</span> <span class="toc-text">3.1 事务解决的问题</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#3-1-1-脏读"><span class="toc-number">3.1.1.</span> <span class="toc-text">3.1.1 脏读</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#3-1-2-不可重复读"><span class="toc-number">3.1.2.</span> <span class="toc-text">3.1.2  不可重复读</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#3-1-3-幻读"><span class="toc-number">3.1.3.</span> <span class="toc-text">3.1.3 幻读</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-InnoDB锁算法"><span class="toc-number">4.</span> <span class="toc-text">4. InnoDB锁算法</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#4-1-记录锁-Record-Lock"><span class="toc-number">4.1.</span> <span class="toc-text">4.1 记录锁(Record-Lock)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-2-间隙锁-Gap锁"><span class="toc-number">4.2.</span> <span class="toc-text">4.2 间隙锁(Gap锁)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-3-next-key锁"><span class="toc-number">4.3.</span> <span class="toc-text">4.3 next-key锁</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-3-意向锁"><span class="toc-number">4.4.</span> <span class="toc-text">4.3 意向锁</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-3-MVVC"><span class="toc-number">4.5.</span> <span class="toc-text">4.3 MVVC</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-死锁"><span class="toc-number">5.</span> <span class="toc-text">5. 死锁</span></a></li></ol>
    </div>
    
    <div class="back-top iconfont-archer">&#xe639;</div>
    <div class="sidebar sidebar-hide">
    <ul class="sidebar-tabs sidebar-tabs-active-0">
        <li class="sidebar-tab-archives"><span class="iconfont-archer">&#xe67d;</span><span class="tab-name">Archive</span></li>
        <li class="sidebar-tab-tags"><span class="iconfont-archer">&#xe61b;</span><span class="tab-name">Tag</span></li>
        <li class="sidebar-tab-categories"><span class="iconfont-archer">&#xe666;</span><span class="tab-name">Cate</span></li>
    </ul>
    <div class="sidebar-content sidebar-content-show-archive">
          <div class="sidebar-panel-archives">
    <!-- 在ejs中将archive按照时间排序 -->
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    <div class="total-and-search">
        <div class="total-archive">
        Total : 18
        </div>
        <!-- search  -->
        
    </div>
    
    <div class="post-archive">
    
    
    
    
    <div class="archive-year"> 2020 </div>
    <ul class="year-list">
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/11</span><a class="archive-post-title" href="/blog/2020/12/11/多线程与高并发-JUC死锁/">多线程与高并发-JUC死锁</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/05</span><a class="archive-post-title" href="/blog/2020/12/05/多线程与高并发-JUC之原子类-同步锁/">多线程与高并发-JUC之原子类&同步锁</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">11/26</span><a class="archive-post-title" href="/blog/2020/11/26/设计模式-责任链模式/">设计模式-责任链模式</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">11/20</span><a class="archive-post-title" href="/blog/2020/11/20/多线程与高并发-CAS-AQS源码分析/">多线程与高并发-CAS&AQS源码分析</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">11/16</span><a class="archive-post-title" href="/blog/2020/11/16/多线程与高并发-可见性-顺序性-原子性/">多线程与高并发-可见性|顺序性|原子性</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">11/11</span><a class="archive-post-title" href="/blog/2020/11/11/设计模式-代理模式/">设计模式-代理模式</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">11/07</span><a class="archive-post-title" href="/blog/2020/11/07/设计模式-工厂模式/">设计模式-工厂模式</a>
        </li>
    
    
    
    
    
        </ul>
    
    <div class="archive-year"> 2019 </div>
    <ul class="year-list">
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">03/03</span><a class="archive-post-title" href="/blog/2019/03/03/Mysql死锁分析/">Mysql死锁分析</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">02/23</span><a class="archive-post-title" href="/blog/2019/02/23/Phoenix部署及应用/">Phoenix部署及应用</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">02/20</span><a class="archive-post-title" href="/blog/2019/02/20/JStorm部署及应用/">JStorm部署及应用</a>
        </li>
    
    
    
    
    
        </ul>
    
    <div class="archive-year"> 2018 </div>
    <ul class="year-list">
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/26</span><a class="archive-post-title" href="/blog/2018/12/26/Storm分布式实时计算框架详解/">Storm分布式实时计算框架详解</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/19</span><a class="archive-post-title" href="/blog/2018/12/19/docker-搭建MongoDb/">docker-搭建MongoDb</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/19</span><a class="archive-post-title" href="/blog/2018/12/19/docker-搭建Mysql/">docker-搭建Mysql</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/15</span><a class="archive-post-title" href="/blog/2018/12/15/git使用总结/">git使用总结</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/13</span><a class="archive-post-title" href="/blog/2018/12/13/linux常用指令分类汇总/">linux常用指令分类汇总</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/13</span><a class="archive-post-title" href="/blog/2018/12/13/HBase之Springboot操作指南/">HBase之Springboot操作指南</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/06</span><a class="archive-post-title" href="/blog/2018/12/06/Hadoop架构分析与实战操作/">Hadoop架构分析与实战操作</a>
        </li>
    
    
        <li class="archive-post-item">
            <span class="archive-post-date">12/06</span><a class="archive-post-title" href="/blog/2018/12/06/Hadoop+HBase集群搭建/">Hadoop+HBase高可用集群搭建</a>
        </li>
    
    </ul></div>
  </div>
        <div class="sidebar-panel-tags">
    <div class="sidebar-tags-name">
    
        <span class="sidebar-tag-name" data-tags="hadoop"><span class="iconfont-archer">&#xe606;</span>hadoop</span>
    
        <span class="sidebar-tag-name" data-tags="storm"><span class="iconfont-archer">&#xe606;</span>storm</span>
    
        <span class="sidebar-tag-name" data-tags="docker"><span class="iconfont-archer">&#xe606;</span>docker</span>
    
        <span class="sidebar-tag-name" data-tags="tools"><span class="iconfont-archer">&#xe606;</span>tools</span>
    
        <span class="sidebar-tag-name" data-tags="mysql"><span class="iconfont-archer">&#xe606;</span>mysql</span>
    
        <span class="sidebar-tag-name" data-tags="linux"><span class="iconfont-archer">&#xe606;</span>linux</span>
    
        <span class="sidebar-tag-name" data-tags="多线程与高并发"><span class="iconfont-archer">&#xe606;</span>多线程与高并发</span>
    
        <span class="sidebar-tag-name" data-tags="设计模式"><span class="iconfont-archer">&#xe606;</span>设计模式</span>
    
    </div>
    <div class="iconfont-archer sidebar-tags-empty">&#xe678;</div>
    <div class="tag-load-fail" style="display: none; color: #ccc; font-size: 0.6rem;">
    缺失模块。<br>
    1、请确保node版本大于6.2<br>
    2、在博客根目录（注意不是archer根目录）执行以下命令：<br>
    <span style="color: #f75357; font-size: 1rem; line-height: 2rem;">npm i hexo-generator-json-content --save</span><br>
    3、在根目录_config.yml里添加配置：
    <pre style="color: #787878; font-size: 0.6rem;">
jsonContent:
  meta: false
  pages: false
  posts:
    title: true
    date: true
    path: true
    text: false
    raw: false
    content: false
    slug: false
    updated: false
    comments: false
    link: false
    permalink: false
    excerpt: false
    categories: true
    tags: true</pre>
    </div> 
    <div class="sidebar-tags-list"></div>
</div>
        <div class="sidebar-panel-categories">
    <div class="sidebar-categories-name">
    
    </div>
    <div class="iconfont-archer sidebar-categories-empty">&#xe678;</div>
    <div class="sidebar-categories-list"></div>
</div>
    </div>
</div> 
    <script>
    var siteMeta = {
        root: "/blog/",
        author: "Eric"
    }
</script>
    <!-- CDN failover -->
    <script src="https://cdn.jsdelivr.net/npm/jquery@3.3.1/dist/jquery.min.js"></script>
    <script type="text/javascript">
        if (typeof window.$ === 'undefined')
        {
            console.warn('jquery load from jsdelivr failed, will load local script')
            document.write('<script src="/blog/lib/jquery.min.js">\x3C/script>')
        }
    </script>
    <script src="/blog/scripts/main.js"></script>
    <!-- algolia -->
    
    <!-- busuanzi  -->
    
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    
    <!-- CNZZ  -->
    
    </div>
    <!-- async load share.js -->
    
        <script src="/blog/scripts/share.js" async></script>    
     
    </body>
</html>


